###############################################################################
#                                                                             #
#    This file is part of the "ssbot" Python module.                          #
#    It is distributed under the MIT License. See LICENSE.txt for details.    #
#                                                                             #
###############################################################################

import os
import os.path
import sqlite3

import ssutils


BUILDS_TABLE_NAME = 'Builds'
BUILD_STEPS_TABLE_NAME_FORMAT = 'Build_%d_Steps'

# Each builder has a table named 'Builds' which has the following columns:
#   'number' - The number of the build,
#   'reason' - Reason as to why the build was triggered,
#   'status' - Whether the build is 'running' or 'completed',
#   'result' - Whether the build has failed or passed,
#   'start_time' - The time when the build started,
#   'end_time' - The time when the build ended.
BUILDS_TABLE_COLUMN_DESCRIPTION = (
'(number text, reason text, status text, result text, start_time text,'
' end_time text)')

BUILDS_STEPS_TABLE_COLUMN_DESCRIPTION = (
'(name text, status text, result text, start_time text, end_time text)')


class BuilderDB(object):
    def __init__(self, builder_name):
        self.db_file = os.path.join('.', builder_name, '%s.db' % builder_name)
        self.builder_name = builder_name
        # Create the DB if not present.
        if not os.path.exists(self.db_file):
            conn = sqlite3.connect(self.db_file)
            # Create the table with name in the var BUILDS_TABLE_NAME.
            c = conn.cursor()
            c.execute('CREATE TABLE %s %s' %
                      (BUILDS_TABLE_NAME, BUILDS_TABLE_COLUMN_DESCRIPTION))
            conn.commit()
            conn.close()

    def get_build_count(self):
        """ Returns the number of build records in the DB.
        """
        conn = sqlite3.connect(self.db_file)
        c = conn.cursor()
        c.execute('SELECT COUNT(*) AS count FROM %s' % BUILDS_TABLE_NAME)
        total_builds = c.fetchone()[0]
        conn.close()
        return total_builds

    def add_new_build(self, build_number, reason):
        """ Adds a record for the new build to the BUILDS_TABLE_NAME table.

        Also creates a new table for the build itself with a name derived
        from BUILD_STEPS_TABLE_NAME_FORMAT.
        """
        steps_table_name = BUILD_STEPS_TABLE_NAME_FORMAT % build_number
        conn = sqlite3.connect(self.db_file)
        c = conn.cursor()
        c.execute(
            'INSERT INTO %s VALUES ("%d", "%s", "%s", "%s", "%s", "%s")' %
            (BUILDS_TABLE_NAME, build_number, reason, ssutils.STATUS_PENDING,
             '-NA-', '-NA-', '-NA-'))
        c.execute('CREATE TABLE %s %s' %
                  (steps_table_name, BUILDS_STEPS_TABLE_COLUMN_DESCRIPTION))
        conn.commit()
        conn.close()

    def start_build(self, build_number, start_time):
        """ Updates record corresponding to |build_number| to running status.

        The start time is also updated.
        """
        conn = sqlite3.connect(self.db_file)
        c = conn.cursor()
        c.execute(
            'UPDATE %s ' % BUILDS_TABLE_NAME +
            'SET status="%s",start_time="%s" '
            'WHERE number="%d"' %
            (ssutils.STATUS_RUNNING, start_time, build_number))
        conn.commit()
        conn.close()

    def finish_build(self, build_number, result, end_time):
        """ Updates record corresponding to |build_number| to completed status.

        The result and end time are also updated.
        """
        conn = sqlite3.connect(self.db_file)
        c = conn.cursor()
        c.execute(
            'UPDATE %s ' % BUILDS_TABLE_NAME +
            'SET status="%s",result="%s",end_time="%s" '
            'WHERE number="%d"' %
            (ssutils.STATUS_COMPLETED, result, end_time, build_number))
        conn.commit()
        conn.close()

    def add_step(self, build_number, step_name, start_time):
        """ Adds a step into the build's steps table.
        """
        steps_table_name = BUILD_STEPS_TABLE_NAME_FORMAT % build_number
        conn = sqlite3.connect(self.db_file)
        c = conn.cursor()
        c.execute(
            'INSERT INTO %s VALUES ("%s", "%s", "%s", "%s", "%s")' %
            (steps_table_name, step_name, ssutils.STATUS_RUNNING, '-NA-',
             start_time, '-NA-'))
        conn.commit()
        conn.close()

    def finish_step(self, build_number, step_name, result, end_time):
        """ Updates the record corresponding to |step_name| in the steps table.
        """
        steps_table_name = BUILD_STEPS_TABLE_NAME_FORMAT % build_number
        conn = sqlite3.connect(self.db_file)
        c = conn.cursor()
        c.execute(
            'UPDATE %s ' % steps_table_name +
            'SET status="%s",result="%s",end_time="%s" '
            'WHERE name="%s"' %
            (ssutils.STATUS_COMPLETED, result, end_time, step_name))
        conn.commit()
        conn.close()

    def get_build(self, build_number):
        conn = sqlite3.connect(self.db_file)
        c = conn.cursor()
        c.execute(
            'SELECT * FROM %s WHERE number="%d"' %
            (BUILDS_TABLE_NAME, build_number))
        res = c.fetchone()
        conn.commit()
        conn.close()
        if res:
            return {
                'build_number': int(build_number),
                'reason': res[1],
                'status': res[2],
                'result': res[3],
                'start_time': res[4],
                'end_time': res[5]
            }
        else:
            None

    def get_all_steps(self, build_number):
        if not self.get_build(build_number):
            return None
        steps_table_name = BUILD_STEPS_TABLE_NAME_FORMAT % build_number
        steps = []
        conn = sqlite3.connect(self.db_file)
        c = conn.cursor()
        c.execute('SELECT * FROM %s' % steps_table_name)
        steps = []
        step = c.fetchone()
        while step:
            steps.append({
                'name': step[0],
                'status': step[1],
                'result': step[2],
                'start_time': step[3],
                'end_time': step[4]
            })
            step = c.fetchone()
        conn.commit()
        conn.close()
        return steps

    def get_step(self, build_number, step_name):
        if not self.get_build(build_number):
            return None
        steps_table_name = BUILD_STEPS_TABLE_NAME_FORMAT % build_number
        conn = sqlite3.connect(self.db_file)
        c = conn.cursor()
        c.execute('SELECT * FROM %s WHERE name="%s"' %
                  (steps_table_name, step_name))
        step = c.fetchone()
        conn.commit()
        conn.close()
        if not step:
            return None
        else:
            return {
                'name': step[0],
                'status': step[1],
                'result': step[2],
                'start_time': step[3],
                'end_time': step[4]
            }


SCHEDULER_TABLE_NAME = 'BuildSets'


class SchedulerDB(object):
    def __init__(self, scheduler_name, builder_name_list):
        self.scheduler_name = scheduler_name
        self.db_file = scheduler_name + '.db'
        self.builder_name_list = builder_name_list
        if not os.path.exists(self.db_file):
            columns = '(build_set_number text, reason text'
            for name in builder_name_list:
                columns += ', %s text' % name
            columns += ')'
            conn = sqlite3.connect(self.db_file)
            c = conn.cursor()
            c.execute('CREATE TABLE %s %s' % (SCHEDULER_TABLE_NAME, columns))
            conn.commit()
            conn.close()

    def get_build_set_count(self):
        """ Returns the number of build set records in the DB.
        """
        conn = sqlite3.connect(self.db_file)
        c = conn.cursor()
        c.execute('SELECT COUNT(*) AS count FROM %s' % SCHEDULER_TABLE_NAME)
        total_build_sets = c.fetchone()[0]
        conn.close()
        return total_build_sets

    def add_new_build_set(self, build_set_number, reason, build_number_map):
        conn = sqlite3.connect(self.db_file)
        c = conn.cursor()
        builder_states_quoted = [
            '"%s"' % ssutils.STATUS_RUNNING for b in self.builder_name_list]
        build_number_list = [
            str(build_number_map[name]) for name in self.builder_name_list]
        build_numbers = ', '.join(build_number_list)
        c.execute(
            'INSERT INTO %s VALUES ("%d", "%s", %s)' %
            (SCHEDULER_TABLE_NAME, build_set_number, reason, build_numbers))
        conn.commit()
        conn.close()

    def get_build_set(self, build_set_number):
        conn = sqlite3.connect(self.db_file)
        c = conn.cursor()
        c.execute('SELECT * FROM %s WHERE build_set_number="%s"' %
                  (SCHEDULER_TABLE_NAME, build_set_number))
        build_set = c.fetchone()
        conn.commit()
        conn.close()
        if not build_set:
            return None
        else:
            bs = {
                'build_set_number': build_set_number,
                'reason': build_set[1]
            }
            i = 2
            for builder in self.builder_name_list:
                bs[builder] = int(build_set[i])
                i += 1
            return bs
